﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_CAR_INFO_Get_Min_N_Max_Expense_Dt')
	BEGIN
		DROP Procedure usp_UPDMS_CAR_INFO_Get_Min_N_Max_Expense_Dt
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_CAR_INFO_Get_Min_N_Max_Expense_Dt
**	Desc : 차량별 비용이 등록된 처음 사용일과 마지막 사용일을 조회
**	Test Exec Query : Exec usp_UPDMS_CAR_INFO_Get_Min_N_Max_Expense_Dt 'CAR01'
**	Called by : Car_Dac_UPDMS_CAR_INFO.cs
**	Program ID : Whenever
**	Auth : 송시명
**	Date : 2010-07-12
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_CAR_INFO_Get_Min_N_Max_Expense_Dt]
@ls_car_id nvarchar(max)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT ISNULL(MIN(rslt.Min_Dt), 0) AS Min_Dt,
       ISNULL(DATEPART(YY, MIN(rslt.Min_Dt)), 0) AS Min_Dt_Y,
       ISNULL(RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(MM, MIN(rslt.Min_Dt))), 2), 0) AS Min_Dt_M,
       ISNULL(RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(DD, MIN(rslt.Min_Dt))), 2), 0) AS Min_Dt_D,
       ISNULL(MAX(rslt.Max_Dt), 0) AS Max_Dt,
       ISNULL(DATEPART(YY, MAX(rslt.Max_Dt)), 0) AS Max_Dt_Y,
       ISNULL(RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(MM, MAX(rslt.Max_Dt))), 2), 0) AS Max_Dt_M,
       ISNULL(RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(DD, MAX(rslt.Max_Dt))), 2), 0) AS Max_Dt_D
  FROM (
       SELECT MIN(Refuelling_Dt) AS Min_Dt,
              MAX(Refuelling_Dt) AS Max_Dt
         FROM UPDMS_CAR_REFUELLING WITH(NOLOCK)
        WHERE Car_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_car_id))
       UNION
       SELECT MIN(Service_Dt),
              MAX(Service_Dt)
         FROM UPDMS_CAR_SERVICE WITH(NOLOCK)
        WHERE Car_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_car_id))
       UNION
       SELECT MIN(Biz_Dt),
              MAX(Biz_Dt)
         FROM UPDMS_CRD_ITEM_BIZ WITH(NOLOCK)
        WHERE Account = 'AC024'
       UNION
       SELECT MIN(Reg_Dt),
              MAX(Reg_Dt)
         FROM UPDMS_MNY_IN_OUT_MGT WITH(NOLOCK)
        WHERE Account = 'AC024'
       ) rslt

GO